﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WL_Store.Model;

namespace WL_Store.DAL
{


    /// <summary>
    /// 商品信息
    /// </summary>
   public  class CommodityInfoDAL
    {
        /// <summary>
        /// 查询商品总数
        /// </summary>
        /// <returns></returns>
        public object CountBlurbs()
        {
            string sql = @"select COUNT(*) from Blurb";

           return  Dbhelper.ExecuteScalar(sql);
        
        }


        /// <summary>
        /// 商品信息查询
        /// </summary>
        /// <returns></returns>
        public List<CommodityInfoModel> GetCommodityInfos(CommodityInfoSearchModel searchModel)
        {
            string sql = "select Blurb.commodityID,Blurb.comName,Blurb.purchase,Blurb.selling,Blurb.productionPlace,Blurb.stocks,Blurb.quantitySold,Blurb.description,Blurb.addTime from Blurb";

            //构造where的泛型集合
            List<string> listWhere = new List<string>();
            //构造参数化查询数组的泛型集合
            List<SqlParameter> listParameters = new List<SqlParameter>();
            //判断多条件查询的条件商品编号是否输入
            if (!string.IsNullOrWhiteSpace(searchModel.Id))
            {
                listWhere.Add("Blurb.commodityID=@commodityID");
                listParameters.Add(new SqlParameter("@commodityID", searchModel.Id));
            }
            //判断多条件查询的条件商品名称是否输入
            if (!string.IsNullOrWhiteSpace(searchModel.name))
            {
                listWhere.Add("Blurb.comName=@comName");
                listParameters.Add(new SqlParameter("@comName", searchModel.name));
            }
            if (searchModel.StartAddtime.HasValue)
            {
                listWhere.Add("Blurb.addTime>=@StartAddtime");
                listParameters.Add(new SqlParameter("@StartAddtime", searchModel.StartAddtime));
            }
            if (searchModel.EndAddtime.HasValue)
            {
                listWhere.Add("Blurb.addTime<=@EndAddtime");
                listParameters.Add(new SqlParameter("@EndAddtime", searchModel.EndAddtime));
            }


            //判断检索管理员信息是否为空
            if (listWhere.Count > 0)
            {
                sql += " where " + string.Join(" and ", listWhere.ToArray());
            }




            SqlDataReader readers = Dbhelper.ExecuteReader(sql,listParameters.ToArray());
            //构造出一个泛型集合
            List<CommodityInfoModel> list = new List<CommodityInfoModel>();
            //判断游标中是否有数据行
            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    CommodityInfoModel model = new CommodityInfoModel();
                    model.commodityID = Convert.ToInt32(readers["commodityID"]);
                    model.comName = readers["comName"].ToString();
                    model.purchase = readers["purchase"].ToString();
                    model.selling = readers["selling"].ToString();
                    model.productionPlace = readers["productionPlace"].ToString();
                    model.stocks = Convert.ToInt32(readers["stocks"]);
                    model.quantitySold = Convert.ToInt32(readers["quantitySold"]);
                    model.description = readers["description"].ToString();
                    model.addTime = Convert.ToDateTime(readers["addTime"]);
                    //追加到集合中
                    list.Add(model);
                }
                //关闭游标
                readers.Close();
            }
            return list;

        }
        #region DataTable
        //public DataTable GetCommodityInfos()
        //{
        //    string sql = "select Blurb.commodityID,Blurb.comName,Blurb.purchase,Blurb.selling,Blurb.productionPlace,Blurb.stocks,Blurb.quantitySold,Blurb.description,Blurb.addTime from Blurb";

        //    return Dbhelper.ExecuteTable(sql);

        //}
        #endregion

            /// <summary>
            /// 添加商品 返回true：添加成功 返回false：添加失败
            /// </summary>
            /// <param name="commod"></param>
            /// <returns></returns>
        public bool AddCommodityInfos(CommodityInfoModel commod)
        {
            string sql = @"insert into Blurb(commodityID,comName,purchase,selling,productionPlace,stocks,quantitySold,description,addTime) values
(@commodityID,@comName,@purchase,@selling,@productionPlace,@stocks,@quantitySold,@description,@addTime)";
            SqlParameter[] parameters = {
            new SqlParameter("@commodityID",commod.commodityID),
            new SqlParameter("@comName",commod.comName),
            new SqlParameter("@purchase",commod.purchase),
            new SqlParameter("@selling",commod.selling),
            new SqlParameter("@productionPlace",commod.productionPlace),
            new SqlParameter("@stocks",commod.stocks),
            new SqlParameter("@quantitySold",commod.quantitySold),
            new SqlParameter("@description",commod.description),
            new SqlParameter("@addTime",commod.addTime)
            };

            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }
        /// <summary>
        /// 删除商品信息 返回true：删除成功；返回false：删除失败 
        /// </summary>
        /// <param name="commodityID"></param>
        /// <returns></returns>
        public bool DeleteCommodityInfo(int commodityID)
        {
            string sql = @"delete from Blurb where commodityID=@commodityID";
            SqlParameter[] parameters = {
            new SqlParameter("@commodityID",commodityID)
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        /// <summary>
        /// 根据commodityID查询信息
        /// </summary>
        /// <param name="commodityID"></param>
        /// <returns></returns>
        public CommodityInfoModel commodity(int commodityID) {
            string sql = @"select*from Blurb where comId=@commodityID";
            SqlParameter[] parameters = { 
            new SqlParameter("@commodityID",commodityID)
            };

            SqlDataReader readers = Dbhelper.ExecuteReader(sql,parameters);
            //判断游标中是否有数据行
            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    CommodityInfoModel model = new CommodityInfoModel();
                    model.commodityID = Convert.ToInt32(readers["comId"]);
                    model.comName = readers["comName"].ToString();
                    model.purchase = readers["purchase"].ToString();
                    model.selling = readers["selling"].ToString();
                    model.productionPlace = readers["productionPlace"].ToString();
                    model.stocks = Convert.ToInt32(readers["stocks"]);
                    model.quantitySold = Convert.ToInt32(readers["quantitySold"]);
                    model.description = readers["description"].ToString();
                    model.addTime = Convert.ToDateTime(readers["addTime"]);
                    //关闭游标
                    readers.Close();
                    return model;
                }
               
            }
            return null;


        }


        /// <summary>
        /// 根据commodityID更改商品信息 返回ture：更改成功；返回false：更改失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateCommodityInfo(CommodityInfoModel model)
        {
            string sql = @"update Blurb set comName=@comName,purchase=@purchase,selling=@selling,productionPlace=@productionPlace,stocks=@stocks,quantitySold=@quantitySold,description=@description where commodityID=@commodityID";

            SqlParameter[] parameters = { 
            new SqlParameter("@comName",model.comName),
            new SqlParameter("@purchase",model.purchase),
            new SqlParameter("@selling",model.selling),
            new SqlParameter("@productionPlace",model.productionPlace),
            new SqlParameter("@stocks",model.stocks),
            new SqlParameter("@quantitySold",model.quantitySold),
            new SqlParameter("@description",model.description),
            new SqlParameter("@commodityID",model.commodityID),
            };
            return Dbhelper.ExecuteNonQuery(sql,parameters)>0;
        }

        /// <summary>
        /// 商品信息查询（proc）(分页)
        /// </summary>
        /// <returns></returns>
        public List<CommodityInfoModel> GetCommodityInfosProc(CommodityInfoSearchModel searchModel, int PageIndex, int PageSize, out int TotalCount)
        {
            TotalCount = 0;
            string procName = "PROC_Blurbs";

            SqlParameter[] parameter =
                {
             new SqlParameter("@commodityID",searchModel.Id),
             new SqlParameter("@comName",searchModel.name),
             new SqlParameter("@StartAddtime",searchModel.StartAddtime.HasValue?searchModel.StartAddtime:(object)DBNull.Value),
             new SqlParameter("@EndAddtime",searchModel.EndAddtime.HasValue?searchModel.EndAddtime:(object)DBNull.Value),
             new SqlParameter("@categoryId",searchModel.categoryId),
             new SqlParameter("@PageIndex",PageIndex),
             new SqlParameter("@PageSize",PageSize),
             new SqlParameter("@TotalCount",TotalCount)
            };

            //为存储过程中输入类型参数设定为output
            parameter[7].Direction = ParameterDirection.Output;

            SqlDataReader readers = Dbhelper.ExecuteReaderProc(procName,parameter);
            //构造出一个泛型集合
            List<CommodityInfoModel> list = new List<CommodityInfoModel>();
            //判断游标中是否有数据行
            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    CommodityInfoModel model = new CommodityInfoModel();
                    model.commodityID = Convert.ToInt32(readers["comId"]);
                    model.comName = readers["comName"].ToString();
                    model.categoryId = Convert.ToInt32(readers["categoryId"]);
                    model.category = readers["category"].ToString();
                    model.purchase = readers["purchase"].ToString();
                    model.selling = readers["selling"].ToString();
                    model.productionPlace = readers["productionPlace"].ToString();
                    model.stocks = Convert.ToInt32(readers["stocks"]);
                    model.quantitySold = Convert.ToInt32(readers["quantitySold"]);
                    model.description = readers["description"].ToString();
                    model.addTime = Convert.ToDateTime(readers["addTime"]);
                    //追加到集合中
                    list.Add(model);
                }
                //关闭游标
                readers.Close();
                //获取存储过程中的输出类型参数的值，一定要在游标关闭之后！！！！！
                TotalCount = Convert.ToInt32(parameter[7].Value);
            }
            return list;

        }

        /// <summary>
        /// 添加商品 返回true：添加成功 返回false：添加失败(proc)
        /// </summary>
        /// <param name="commod"></param>
        /// <returns></returns>
        public bool AddCommodityInfosProc(CommodityInfoModel commod)
        {
            string procName = @"PROC_AddBlurbs";
            SqlParameter[] parameters = {
            new SqlParameter("@commodityID",commod.commodityID),
            new SqlParameter("@comName",commod.comName),
            new SqlParameter("@purchase",commod.purchase),
            new SqlParameter("@selling",commod.selling),
            new SqlParameter("@productionPlace",commod.productionPlace),
            new SqlParameter("@stocks",commod.stocks),
            new SqlParameter("@quantitySold",commod.quantitySold),
            new SqlParameter("@description",commod.description),
            new SqlParameter("@addTime",commod.addTime),
             new SqlParameter("@categoryId",commod.categoryId),
            };

            return Dbhelper.ExecuteNonQueryProc(procName, parameters) > 0;
        }


        /// <summary>
        /// 删除商品信息 返回true：删除成功；返回false：删除失败 (proc)
        /// </summary>
        /// <param name="commodityID"></param>
        /// <returns></returns>
        public bool DeleteCommodityInfoProc(int commodityID)
        {
            string procName = @"PROC_DeleteBlurbs";
            SqlParameter[] parameters = {
            new SqlParameter("@commodityID",commodityID)
            };
            return Dbhelper.ExecuteNonQueryProc(procName, parameters) > 0;
        }

        /// <summary>
        /// 根据commodityID更改商品信息 返回ture：更改成功；返回false：更改失败(proc)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateCommodityInfoProc(CommodityInfoModel model)
        {
            string sql = @"PROC_UpdateBlurbs";

            SqlParameter[] parameters = {
            new SqlParameter("@comName",model.comName),
            new SqlParameter("@purchase",model.purchase),
            new SqlParameter("@selling",model.selling),
            new SqlParameter("@productionPlace",model.productionPlace),
            new SqlParameter("@stocks",model.stocks),
            new SqlParameter("@quantitySold",model.quantitySold),
            new SqlParameter("@description",model.description),
            new SqlParameter("@commodityID",model.commodityID),
            };
            return Dbhelper.ExecuteNonQueryProc(sql, parameters) > 0;
        }

        /// <summary>
        ///查询商品类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetCategory()
        {
            string sql = @"select*from GoodsType";
            return Dbhelper.ExecuteTable(sql);
        }

        /// <summary>
        /// 查询商品编号在数据库中是否存在，存在返回1,不存在返回0；
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public object GetBlurbComId(string comId)
        {
            string sql = @"select COUNT(*) from Blurb where comId=@comId";

            SqlParameter[] parameters = {
           new SqlParameter("@comId",comId)
            };
            return Dbhelper.ExecuteScalar(sql, parameters);
        }



    }
}
